package com.scalar.chiptrack.operations.dataentry.dao;

import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.operations.dataentry.InTransitLots;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.ScalarException;

import java.util.ArrayList;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.struts.util.LabelValueBean;

/**
 * Created by Chandra Shekar.
 * Date: Jun 20, 2005
 * Time: 6:58:05 PM
 */
public class MRBReleaseDAO implements AdaptorConstants
{

    public static ArrayList getAllMRBReleaseLots() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList mrbReleaseLotsList = new ArrayList();
        ResultSet rset = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "{ CALL SP_GET_WAFERS_FOR_MRB_RELEASE }" );
            rset = pstmt.executeQuery();
            InTransitLots inTransitLots = null;

            while ( rset.next() )
            {
                inTransitLots = new InTransitLots();
                inTransitLots.setLotID( rset.getInt( "LOT_ID") ) ;
                inTransitLots.setPartNumber( rset.getString( "PART_NUMBER" ) );
                inTransitLots.setLotNumber( rset.getString( "LOT_NUMBER" ) );
                inTransitLots.setShipQty( rset.getString( "GOOD_DICE" ) );
                inTransitLots.setWaferId( rset.getString("WAFER_ID") );
                inTransitLots.setBin1( rset.getInt("BIN_1") );
                inTransitLots.setBin2( rset.getInt("BIN_2") );
                inTransitLots.setBin3( rset.getInt("BIN_3") );
                inTransitLots.setBin4( rset.getInt("BIN_4") );
                inTransitLots.setBin5( rset.getInt("BIN_5") );
                inTransitLots.setBin6( rset.getInt("BIN_6") );
                inTransitLots.setBin7( rset.getInt("BIN_7") );
                inTransitLots.setBin8( rset.getInt("BIN_8") );
                inTransitLots.setBin9( rset.getInt("BIN_9") );
                inTransitLots.setBin10( rset.getInt("BIN_10") );
                inTransitLots.setBin11( rset.getInt("BIN_11") );
                inTransitLots.setBin12( rset.getInt("BIN_12") );
                inTransitLots.setBin13( rset.getInt("BIN_13") );
                inTransitLots.setBin14( rset.getInt("BIN_14") );
                inTransitLots.setBin15( rset.getInt("BIN_15") );
                inTransitLots.setLocation( rset.getString("L_VENDOR_NAME"));
                inTransitLots.setDateOut( rset.getString("DATE_OUT"));

                mrbReleaseLotsList.add( inTransitLots );
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load all MRB Release Lots from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
        return mrbReleaseLotsList;
    }

    public static void updateReleaseMRBStatus ( int lotId, String waferId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = true;


        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "UPDATE INFORMATIVE_BINNING SET MRB_LIMIT = 1 WHERE LOT_ID = ? AND WAFER_ID = ?" );
            pstmt.setInt(1, lotId);
            pstmt.setString( 2, waferId );
            pstmt.executeUpdate();
            success = true;
        }
        catch ( Exception sqle )
        {
            success = false;
            sqle.printStackTrace();
            throw new ScalarException( "Could not UPDATE MRB Limit Status in the database", sqle );
        }

        finally
        {

            try
            {
               if ( conn != null )
               {
                    if ( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch ( SQLException ignore )
            {
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
    }

    public static void insertSortBinReleaseLots (DBTransaction dbTransaction, InTransitLots  relesedLots ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        CallableStatement cstmt = null;
        boolean success = true;


        try
        {
            if( dbTransaction != null)
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt =  conn.prepareStatement( "DELETE FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = ? AND WAFER_ID = ? \n" +
                    "AND ( SELECT COUNT(*) FROM ASSM_RELEASE_LOT_TABLE ARLT, ASSM_RELEASE_TABLE ART WHERE \n" +
                    "ARLT.ASSEMBLY_RELEASE_ID = ART.ASSEMBLY_RELEASE_ID AND ART.STATUS <> 'CANCELLED' AND ARLT.LOT_ID = ? AND ARLT.WAFER_ID = ?) = 0");
            pstmt.setInt(1, relesedLots.getLotID() );
            pstmt.setString(2, relesedLots.getWaferId() );
            pstmt.setInt(3, relesedLots.getLotID() );
            pstmt.setString(4, relesedLots.getWaferId() );
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;

            cstmt =  conn.prepareCall( "{ CALL SP_DOWNGRADE_DIEBANK_BINS ( ?,? ) }");

            cstmt.setInt( 1, relesedLots.getLotID() );
            cstmt.setString(2, relesedLots.getWaferId() );

            cstmt.executeUpdate();
            success = true;
        }
        catch ( Exception sqle )
        {
            success = false;
            sqle.printStackTrace();
            throw new ScalarException( "Could not INSERT Sort Bin Release in to the database", sqle );
        }

        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    if ( success )
                    {
                        try
                        {
                            conn.commit();
                        }
                        catch ( SQLException e )
                        {
                            throw new ScalarException( "Could not commit MRB Release", e );
                        }
                    }
                    else
                    {
                        try
                        {
                            conn.rollback();
                        }
                        catch ( SQLException e )
                        {
                            throw new ScalarException( "Could not rollback MRB Release", e );
                        }
                    }
                }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); }catch( SQLException ignore ) { }
            }

            if ( cstmt != null )
            {
                try { cstmt.close(); }catch( SQLException ignore ) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch( SQLException  ignore ) { }
            }
        }
    }

    public static ArrayList getMoveToOptions( )
    {
        ArrayList moveToOptions = new ArrayList();

        moveToOptions.add ( new LabelValueBean( "Release", Constants.MRBRELEASE_MOVE_TO_RELEASE ) );
        moveToOptions.add ( new LabelValueBean( "RTV", Constants.MRBRELEASE_MOVE_TO_RTV ) );
        moveToOptions.add ( new LabelValueBean( "Scrap", Constants.MRBRELEASE_MOVE_TO_SCRAP ));
        moveToOptions.add ( new LabelValueBean( "Engg", Constants.MRBRELEASE_MOVE_TO_ENGG ));

        return moveToOptions;
    }

    public static void updateReleaseMRBStatus ( DBTransaction dbTransaction, ArrayList releaseLotsList ) throws ScalarException
    {
        Connection conn     = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        boolean isBatchExists = false;
        InTransitLots inTransitLots = null;
        if ( releaseLotsList == null || releaseLotsList.size() == 0 )
        {
            return;
        }
        int releaseLotsListSize = releaseLotsList.size();
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement ( "{ CALL SP_UPDATE_RELEASE_MRB_STATUS ( ?, ?, ?, ? ) }" );
            for ( int i = 0; i < releaseLotsListSize; i++ )
            {
                inTransitLots = ( InTransitLots ) releaseLotsList.get( i );
                if ( inTransitLots.isChecked() == true )
                {
                    pstmt.setInt ( 1, inTransitLots.getLotID() );
                    pstmt.setString ( 2, inTransitLots.getWaferId() );
                    pstmt.setString ( 3, inTransitLots.getMoveTo() );
                    pstmt.setString ( 4, inTransitLots.getComments() );
                    pstmt.addBatch();
                    isBatchExists = true;
                }
            }

            if ( isBatchExists )
            {
                pstmt.executeBatch();
                success = true;
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException( "Could not update MRB Release Lots", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    if ( success )
                    {
                        try
                        {
                            conn.commit();
                        }
                        catch ( SQLException e )
                        {
                            throw new ScalarException( "Could not commit MRB Release", e );
                        }
                    }
                    else
                    {
                        try
                        {
                            conn.rollback();
                        }
                        catch ( SQLException e )
                        {
                            throw new ScalarException( "Could not rollback MRB Release", e );
                        }
                    }
                }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); }catch( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch( SQLException  ignore ) { }
            }
        }
    }
}
